﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class ServiceCheckTooManyDatabaseOwners :IssueTypeCheckBase
    {

        public ServiceCheckTooManyDatabaseOwners()
        {
            IssueTypeId = 2002;
        }

        public override void Check()
        {
            HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            Issue issue;
            
            // Get all Service instance that has ServiceTypeID 1
            
            var Qryinstances = from s in dc.Services
                                    
                
                               where s.ServiceTypeID == 1
                               
                               select s;
            ThresholdValue thresholdValue = dc.ThresholdValues.FirstOrDefault(t => t.IssueTypeID == IssueTypeId && t.ThresholdValueID == 1);
          // Check number of Logins for each instance    

            foreach (Service instance in Qryinstances)
            {

                int count = 0;


                count = GetDatabaseOwnerCount(instance);
             

                if(count > thresholdValue.Value)
                {
                    

                    //Get Issuetype object

                    issue = new Issue();

                    //Get Issuetype object  And Create a new  Global issue object 
                    IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);

                    issue.IssueType = issueType;
                    issue.TargetId = instance.ServiceID;
                    issue.Description = issueType.Description.Replace("{InstanceName}", instance.InstanceName);

                    dc.Issues.InsertOnSubmit(issue);  
                
                }

                dc.SubmitChanges();    
            }          
        
        
        
        
        
        
        }

        public int GetDatabaseOwnerCount(Service instance)
        {
           
            var qryOwnerAccount = from d in instance.Databases

                                  where d.Owner.ToLower() != "sa"

                                  select d.Owner;


            return qryOwnerAccount.Distinct().Count();
        }
      
    }
       
}
